using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SEO.Model;
using SEO.DBSqlHelp;

namespace SEO.DAL
{

    /// <summary>
    /// 版权所有: 版权所有(C) 2011，上海云里网络科技有限公司
    /// 内容摘要: EDMSmtpDAL
    /// 完成日期：2011年7月29日
    /// 版    本：V1.0 
    /// 作    者：Adin
    /// </summary>
    public class EDMSmtpDAL
    {

        /// <summary>
        /// 获取EDMSmtp表中数据
        /// <returns>返回List</returns>
        /// </summary>
        public List<EDMSmtp> GetAllEDMSmtpDAL()
        {
            List<EDMSmtp> list = new List<EDMSmtp>();
            string strSql = "select * from EDMSmtp";
            try
            {
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    EDMSmtp ep = new EDMSmtp();
                    ep.SmtpId = int.Parse(dr["smtp_id"].ToString());
                    ep.EmailAddress = dr["email_address"].ToString();
                    ep.SmtpServer = dr["smtp_server"].ToString();
                    ep.EmailUsername = dr["email_username"].ToString();
                    ep.EmailPassword = dr["email_password"].ToString();
                    ep.SmtpCredential = dr["smtp_credential"].ToString();
                    ep.IsSsl = int.Parse(dr["is_ssl"].ToString());
                    ep.IsOk = int.Parse(dr["is_ok"].ToString());
                    ep.UserId = int.Parse(dr["user_id"].ToString());

                    list.Add(ep);
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMSmtp -> " + ex.Message);
            }
            return list;
        }

        /// <summary>
        /// 根据指定SQL语句获取EDMSmtp表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回List</returns>
        public List<EDMSmtp> GetEDMSmtpByWhereDAL(string strSql)
        {
            List<EDMSmtp> list = new List<EDMSmtp>();
            try
            {
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    EDMSmtp ep = new EDMSmtp();
                    ep.SmtpId = int.Parse(dr["smtp_id"].ToString());
                    ep.EmailAddress = dr["email_address"].ToString();
                    ep.SmtpServer = dr["smtp_server"].ToString();
                    ep.EmailUsername = dr["email_username"].ToString();
                    ep.EmailPassword = dr["email_password"].ToString();
                    ep.SmtpCredential = dr["smtp_credential"].ToString();
                    ep.IsSsl = int.Parse(dr["is_ssl"].ToString());
                    ep.IsOk = int.Parse(dr["is_ok"].ToString());
                    ep.UserId = int.Parse(dr["user_id"].ToString());

                    list.Add(ep);
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMSmtp -> " + ex.Message);
            }
            return list;
        }

        /// <summary>
        /// 往EDMSmtp表中插入数据
        /// <param name="ep">实例化EDMSmtp类</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int InsertEDMSmtpDAL(SEO.Model.EDMSmtp ep)
        {
            int iNum = 0;
            string strSql = "insert into EDMSmtp(email_address,smtp_server,email_username,email_password,smtp_credential,is_ssl,is_ok,user_id) " +
             " values('" + ep.EmailAddress + "','" + ep.SmtpServer + "','" + ep.EmailUsername + "','" + ep.EmailPassword + "','" + ep.SmtpCredential + "'," + ep.IsSsl + "," + ep.IsOk + "," + ep.UserId + ")";
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMSmtp -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 根据指定条件删除EDMSmtp表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int DeleteEDMSmtpDAL(string strSql)
        {
            int iNum = 0;
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMSmtp -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 更新EDMSmtp表中数据
        /// </summary>
        /// <param name="ep">实例化EDMSmtp类</param>
        /// <returns>返回影响行数</returns>
        public int UpdateEDMSmtpDAL(SEO.Model.EDMSmtp ep)
        {
            int iNum = 0;
            string strSql = "update EDMSmtp set send_status='" + ep.PostStatus + "' where smtp_id=" + ep.SmtpId;
            //SEO.Utility.Log.SaveErrorLog("SQL>>>>" + strSql);
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMSmtp -> " + ex.Message);
            }
            return iNum;
        }

        public int UpdateEDMSmtpNewDAL(SEO.Model.EDMSmtp ep)
        {
            int iNum = 0;
            string strSql = "update EDMSmtp set email_address='" + ep.EmailAddress + "',smtp_server='" + ep.SmtpServer + "',email_username='" + ep.EmailUsername + "',email_password='" + ep.EmailPassword + "' where smtp_id=" + ep.SmtpId;
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMSmtp -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 绑定表EDMSmtp到DataGridView
        /// <param name="dgv">DataGridView控件名称</param>
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        public void BindEDMSmtpDAL(System.Windows.Forms.DataGridView dgv, string strSql)
        {
            try
            {
                int iNum = 1;
                //清除最后自动生成的行
                dgv.AutoGenerateColumns = false;
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds, "EDMSmtp");
                ds.Tables["EDMSmtp"].Columns.Add("SSLStatus", typeof(string));
                ds.Tables["EDMSmtp"].Columns.Add("OKStatus", typeof(string));
                ds.Tables["EDMSmtp"].Columns.Add("ViewID", typeof(string));
                for (int i = 0; i < ds.Tables["EDMSmtp"].Rows.Count; i++)
                {
                    DataRow dr = ds.Tables["EDMSmtp"].Rows[i];
                    dr["ViewID"] = iNum;
                    string strSSLStatus = dr["is_ssl"].ToString();
                    if (strSSLStatus == "0")
                    {
                        dr["SSLStatus"] = "是";
                    }
                    else
                    {
                        dr["SSLStatus"] = "否";
                    }

                    string strOKStatus = dr["is_ok"].ToString();
                    if (strOKStatus == "0")
                    {
                        dr["OKStatus"] = "是";
                    }
                    else
                    {
                        dr["OKStatus"] = "否";
                    }

                    dr["email_password"] = "******";

                    iNum++;
                }
                dgv.DataSource = ds.Tables["EDMSmtp"].DefaultView;
                da.Dispose();
                cn.Close();
                //清除当前选择行行为
                dgv.CurrentCell = null;

            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMSmtp -> " + ex.Message);
            }
        }


        /// <summary>
        /// 更新所有SMTP邮件账号为未发布状态
        /// </summary>
        /// <returns></returns>
        public int UpdateAllEDMSmtpDALPostStatusDAL()
        {
            int iNum = 0;
            string strSql = "update EDMSmtp set send_status='0' ";
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定更新所有邮件内容为未发布状态 -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 更新SMTP信息设置状态
        /// </summary>
        /// <param name="strSmtpID"></param>
        /// <param name="strStatus"></param>
        /// <returns></returns>
        public int UpdateEDMSmtpDALStatusDAL(string strSmtpID, string strStatus)
        {
            int iNum = 0;
            string strSql = "update EDMSmtp set is_ok=" + strStatus;
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定更新SMTP信息状态 -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="strID">根据ID删除数据</param>
        /// <returns></returns>
        public int DeleteEDMSmtpByID(string strID)
        {
            int iNum = 0;
            string strSql = "delete from EDMSmtp where smtp_id=" + strID;
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("删除数据 -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 导出SMTP数据到Txt
        /// </summary>
        /// <returns>返回结果</returns>
        public string ExportEDMSMTPForTxt(string strUserID)
        {
            string strReturnValue = "";
            try
            {
                int k = 1;
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                string strSql = "SELECT * FROM EDMSmtp WHERE  (user_id = " + strUserID + ") ORDER BY smtp_id";
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    //email_address,smtp_server,email_username,email_password
                    string strEamilAddress = dr["email_address"].ToString();
                    string strSMTPServer = dr["smtp_server"].ToString();
                    string strUserName = dr["email_username"].ToString();
                    string strPassword = dr["email_password"].ToString();
                    string strValue = k + "	" + strEamilAddress + "	" + strSMTPServer + "	" + strUserName + "	" + strPassword + "\r\n";
                    strReturnValue += strValue;
                    k++;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("导出SMTP数据到Txt -> " + ex.Message);
            }

            return "0	邮件地址	SMTP服务器	用户名	密码\r\n" + strReturnValue;

        }


    }
}
